SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE Procedure [dbo].[SP_TMSABA_MENS_I03]  
/*-------------------------------------------------------*/  
/*---Empresa              : OFISIS S.A.                --*/  
/*---Cliente              : OFISIS                 --*/  
/*---Sistema              : Tesoreria                  --*/  
/*---M¸dulo               : Empresa                    --*/  
/*---Programa             : Reprocesamiento del Mes    --*/  
/*---Script               : tmsaba_m.sql               --*/  
/*---Nombre SP            : SP_TMSABA_MENS_I03         --*/  
/*---Desarrollado por     : Javier Flores A.           --*/  
/*---Fecha Creaci¸n       : 16/07/1999                 --*/  
/*---Base Datos           : Microsoft Sql Server       --*/  
/*---Versi¸n              : 7.0                        --*/  
/*---Invoca a SP          :                            --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Modificado 1 por     : DCH                            --*/  
/*---Fecha Modificaci¸n   : 9/11/2004                       --*/   
/*---Detalle Modificaci¸n : Actualizacion de Nuevo Mundo   --*/  
/*-------------------------------------------------------*/  
  /*-------------------------------------------------------*/  
/*---Modificado 1 por     : Maribel Julca                            --*/  
/*---Fecha Modificaci¸n   : 18/04/2005                       --*/   
/*---Detalle Modificaci¸n : ingreso de los campos CO_USUA_CREA Y CO_USUA_MODI    --*/  
/*-------------------------------------------------------*/ 
/*-------------------------------------------------------*/  
/*---Comentarios                                       --*/  
/*---                                                  --*/  
/*---                                                  --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Drop Proc SP_TMSABA_MENS_I03                      --*/  
/*---Grant Execute on SP_TMSABA_MENS_I03 to public         --*/   
/*---SP_TMSABA_MENS_I03                                --*/  
/*-------------------------------------------------------*/  
 @ISCO_EMPR TD_VC_002,   
 @ISCO_USUA_MODI TD_VC_008_USUA,  
 @ISCO_BANC TD_VC_003,   
 @ISNU_CNTA_BANC TD_VC_020,  
 @ISAA_BNCO TD_VC_004,   
 @ISMM_BNCO TD_VC_002
	As
Declare  @CSCO_BANC TD_VC_003, @CSNU_CNTA_BANC TD_VC_020,   
          @CSAA_BNCO TD_VC_004, @CSMM_BNCO TD_VC_002,   
          @CNIM_SALD_INIC TD_NU_016_004, @CNIM_INGR TD_NU_016_004, @CNIM_EGRE TD_NU_016_004,  
          @VNCO_0001 TD_IN_001, @VNCO_0002 TD_IN_001,   
          @VNIM_AU01 TD_NU_016_004, @VNIM_AU02 TD_NU_016_004, @CSTI_SITU TD_VC_003  
  
  
UPDATE TMSABA_MENS  
SET IM_INGR = T2.IM_INGR,  
    IM_EGRE = T2.IM_EGRE,
	FE_USUA_MODI = GETDATE(),   
    CO_USUA_MODI = @ISCO_USUA_MODI    
FROM TMSABA_MENS T1, (SELECT  CO_EMPR, CO_BANC, NU_CNTA_BANC, AA_BNCO, MM_BNCO,  
    SUM(PATINDEX(TI_MOVI_BANC, 'I') * IM_MOVI) IM_INGR,  
    SUM(PATINDEX(TI_MOVI_BANC, 'E') * IM_MOVI) IM_EGRE  
   FROM TCMOVI_BANC  
   WHERE CO_EMPR = @ISCO_EMPR  
   AND CO_BANC = @ISCO_BANC  
   AND NU_CNTA_BANC = @ISNU_CNTA_BANC  
   AND AA_BNCO = @ISAA_BNCO  
   AND MM_BNCO = @ISMM_BNCO  
   AND TI_SITU != 'ANU'  
   GROUP BY CO_EMPR, CO_BANC, NU_CNTA_BANC, AA_BNCO, MM_BNCO) T2  
WHERE T1.CO_EMPR = T2.CO_EMPR  
AND T1.CO_BANC = T2.CO_BANC  
AND T1.NU_CNTA_BANC = T2.NU_CNTA_BANC  
AND T1.AA_BNCO = T2.AA_BNCO  
AND T1.MM_BNCO = T2.MM_BNCO  
  
    If @@error <> 0  
       Begin                       
         Rollback Tran  
         Return  
       end       
  
  
  
Begin Tran SP_TMSABA_MENS_I03  
  
  
    Declare CU_TMSABA_MENS_I03 Cursor  
    For   
    Select CO_BANC, NU_CNTA_BANC, AA_BNCO, MM_BNCO, IM_SALD_INIC, IM_INGR, IM_EGRE, TI_SITU  
    From   TMSABA_MENS  
    Where  CO_EMPR = @ISCO_EMPR   
    And    CO_BANC = @ISCO_BANC   
    And    NU_CNTA_BANC = @ISNU_CNTA_BANC   
    And    AA_BNCO+MM_BNCO >= @ISAA_BNCO+@ISMM_BNCO  
                  
    Open  CU_TMSABA_MENS_I03  
    Fetch CU_TMSABA_MENS_I03 into @CSCO_BANC, @CSNU_CNTA_BANC,  
    @CSAA_BNCO, @CSMM_BNCO, @CNIM_SALD_INIC, @CNIM_INGR, @CNIM_EGRE, @CSTI_SITU  
  
    Select @VNCO_0001 = 0, @VNCO_0002 = 0, @VNIM_AU01 = 0, @VNIM_AU02 = 0  
  
    While (@@FETCH_STATUS = 0)  
      Begin  
   If (@@FETCH_STATUS < 0)  
          Begin  
         RAISERROR 20001 'FIN DE ARCHIVO'  
    Close CU_TMSABA_MENS_I03  
    Deallocate CU_TMSABA_MENS_I03  
   Return  
       End  
    
        If @VNCO_0001 >= 1         
           Begin  
             If @CSTI_SITU = 'CER'   
                Begin  
                  Update TMSABA_MENS  
             Set IM_SALD_INIC = @VNIM_AU01,
				 FE_USUA_MODI = GETDATE(),   
                 CO_USUA_MODI = @ISCO_USUA_MODI    
                  Where CO_EMPR = @ISCO_EMPR   
                  AND   CO_BANC = @CSCO_BANC   
                  AND   NU_CNTA_BANC = @CSNU_CNTA_BANC  
                  AND   AA_BNCO = @CSAA_BNCO  
                  AND   MM_BNCO = @CSMM_BNCO  
  
                  If @@error <> 0  
                     Begin                     
                       Close CU_TMSABA_MENS_I03  
                       Deallocate CU_TMSABA_MENS_I03  
                       Rollback Tran  
                       Return  
                     End  
                End  
  
             If @CSTI_SITU = 'ACT'  
Begin  
             Update TMSABA_MENS  
                  Set    IM_SALD_INIC = @VNIM_AU01,
						 FE_USUA_MODI = GETDATE(),   
		                 CO_USUA_MODI = @ISCO_USUA_MODI    
                  Where  CO_EMPR = @ISCO_EMPR   
                  And    CO_BANC = @CSCO_BANC  
                  And    NU_CNTA_BANC = @CSNU_CNTA_BANC  
                  And    AA_BNCO = @CSAA_BNCO  
                  And    MM_BNCO = @CSMM_BNCO                 
  
                  Select @VNCO_0002 = @VNCO_0002 + 1   
    
                  If @@error <> 0  
                     Begin                     
                       Close CU_TMSABA_MENS_I03  
                       Deallocate CU_TMSABA_MENS_I03  
                       Rollback Tran  
                       Return  
                     End  
               End  
       End         
         
       /* --- CALCULAMOS EL SALDO INICIAL PARA LOS DEMAS MESES EN BASE AL TI_SITU ------ */  
       If @VNCO_0002 = 0  
          Begin  
            /*  -------- LA PRIMERA VEZ : CALCULO EL SALDO INICIAL PARA EL SGTE MES --------- */        
     
            If @VNCO_0001 = 0  
               Select @VNIM_AU01 =  (ISNULL(@CNIM_SALD_INIC,0) + ISNULL(@CNIM_INGR,0)) - ISNULL(@CNIM_EGRE,0)  
  
            /* --------- CALCULO : DEL SALDO INICIAL PARA LOS SGTES MESES -------------------- */  
  
            If @VNCO_0001 >= 1  
               Begin  
                 Select @VNIM_AU02 = ( @VNIM_AU01 + ISNULL(@CNIM_INGR,0)) - ISNULL(@CNIM_EGRE,0)  
                 Select @VNIM_AU01 = @VNIM_AU02        
               End  
        
            Fetch CU_TMSABA_MENS_I03 into @CSCO_BANC, @CSNU_CNTA_BANC,  
            @CSAA_BNCO, @CSMM_BNCO, @CNIM_SALD_INIC, @CNIM_INGR, @CNIM_EGRE, @CSTI_SITU  
  
            Select @VNCO_0001 = @VNCO_0001 + 1           
         End    
  
         If @VNCO_0002 = 1  
            Break  
   
   End   /* ----- DEL WHILE ----- */  
  
   Close CU_TMSABA_MENS_I03  
   Deallocate CU_TMSABA_MENS_I03  
  
   /* -------------- TI_SITU = 'CER' , PARA EL PERIODO QUE SE REAPERTURO --------- */   
  
    Update TMSABA_MENS  
    Set    TI_SITU = 'CER',
           FE_USUA_MODI = GETDATE(),   
           CO_USUA_MODI = @ISCO_USUA_MODI    
    Where  CO_EMPR = @ISCO_EMPR   
    And    CO_BANC = @ISCO_BANC   
    And    NU_CNTA_BANC = @ISNU_CNTA_BANC   
    And AA_BNCO = @ISAA_BNCO   
    And    MM_BNCO = @ISMM_BNCO                 
  
    If @@error <> 0  
       Begin                       
         Rollback Tran  
         Return  
       end       
  
Commit Tran  
  
/*-------------------------- Fin -----------------------------*/


GO
